package edu.tomee.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


public class DatabaseConnection {
	public int insert(String fnm,String lnm,String phn,String eid,String pwd,String addr) throws SQLException{
		int result = 0;
		Connection conn = ConnectionManager.getConnection();
		try {
			PreparedStatement ps = conn.prepareStatement("insert into userinfo "
					+ "(first_name, last_name, phone, email, password, address) "
					+ "values (?, ?, ?, ?, ?, ?)");
			ps.setString(1, fnm);
			ps.setString(2, lnm);
			ps.setString(3, phn);
			ps.setString(4, eid);
			ps.setString(5, pwd);
			ps.setString(6, addr);
			result = ps.executeUpdate();
			ps.close();
			if(result>0){
				System.out.println("Inserted Sucessfully");
			}
			else{
				System.out.println("Invalid Entry");
			}
		}
		catch (SQLException e) {
			System.out.println("There is an error"+e.getMessage());
			e.printStackTrace();
			throw(e);
		}finally{
			conn.close();
		}
		
		return result;
	}
	
	public UserInfo logincheck(String eid, String pwd){
		Connection con = ConnectionManager.getConnection();
		UserInfo ui = null;
		
		try {
			PreparedStatement ps = con.prepareStatement("select * from userinfo where email=? and password=?");
			ps.setString(1, eid);
			ps.setString(2, pwd);
			ResultSet rs = ps.executeQuery();
			while( rs.next() ){
				ui = new UserInfo();
				ui.setFname(rs.getString("first_name"));
				ui.setLname(rs.getString("last_name"));
				ui.setEid(rs.getString("email"));
				ui.setPhn(rs.getString("phone"));
				ui.setPwd(rs.getString("password"));
				ui.setAddrs(rs.getString("address"));
				ui.setUid(rs.getInt("id"));
			}
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return ui;
	}
	
	public UserInfo forgetPwd(String eid){
		Connection conn = ConnectionManager.getConnection();
		UserInfo ui = null;
		try {
			PreparedStatement ps = conn.prepareStatement("select * from userinfo where email=?");
			ps.setString(1, eid);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				ui = new UserInfo();
				ui.setPwd(rs.getString("password"));
				ui.setEid(rs.getString("email"));
				ui.setFname(rs.getString("first_name"));
			}
			ps.close();
			rs.close();
			conn.close();
		} 
		catch (SQLException e) {
			e.printStackTrace();
		}
		return ui;
		}
	
	public int sendmsg(String msgbdy,int sender, String receiver, int status){
		Connection conn = ConnectionManager.getConnection();
		int msg_rslt=0;
		try {
			PreparedStatement ps = conn.prepareStatement("insert into mailbox "
									+"(msg_bdy, sender, receiver, status, time) "
									+"values (?, ?, ?, ?, ?)");
			System.out.println("getting ibn");
			ps.setString(1, msgbdy);
			ps.setInt(2, sender);
			ps.setString(3, receiver);
			ps.setInt(4, status);
			ps.setTimestamp(5, new Timestamp(System.currentTimeMillis()));
			msg_rslt = ps.executeUpdate();
			if(msg_rslt==0){
				
				System.out.println("Invalid entry");
			}
			else{
				System.out.println("Successfull insertion");
			}
			ps.close();
			conn.close();
		} 
		catch (SQLException e) {
			System.out.println("There is an error"+e.getMessage());
			e.printStackTrace();
		}
		return msg_rslt;
		
	}
	public List<MsgBox> getMsgs(int uid){
		System.out.println("Checking email for "+uid);
		Connection conn = ConnectionManager.getConnection();
		List<MsgBox> messages = new ArrayList<MsgBox>();
		try {
			PreparedStatement ps = conn.prepareStatement("select * from mailbox WHERE"
					+ " receiver ="+uid);
			ResultSet rs = ps.executeQuery();
			MsgBox mb = null;
			while(rs.next()){
				mb = new MsgBox();
				mb.setId(rs.getString("u_id"));
				mb.setSender(rs.getString("sender"));
				mb.setStatus(rs.getString("status"));
				mb.setMessage(rs.getString("msg_bdy"));
				mb.setTime(rs.getTimestamp("time").toString());
				messages.add(mb);
			}
			rs.close();
			ps.close();
		} 
		catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		System.out.println("messages " + messages);
		return messages;
	}

	public List<UserInfo> allUsers(int currentUserId) {
		Connection con = ConnectionManager.getConnection();
		List<UserInfo> list = new ArrayList<UserInfo>();
		UserInfo ui =null;
		try {
			PreparedStatement ps = con.prepareStatement("SELECT id,first_name FROM userinfo");
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				if(rs.getInt("id") != currentUserId){
					ui = new UserInfo();
					ui.setUid(rs.getInt("id"));
					ui.setFname(rs.getString("first_name"));
					list.add(ui);
				}
			}
			rs.close();
			ps.close();
			con.close();
		} 
		catch (SQLException e) {
			e.printStackTrace();
		}
		System.out.println("list "+ list);
		return list;
	}
	public int pwdChange(String nw_pwd, int uid, String oldPwd){
		Connection co  = ConnectionManager.getConnection();
		int rslt = 0;
		try {
			PreparedStatement ps = co.prepareStatement("update userinfo set password = '"+nw_pwd+"' where id = '"+uid+"' and password= '"+oldPwd+"'");
			rslt = ps.executeUpdate();
			if(rslt>0){
				System.out.println("Inserted Sucessfully");
			}
			else{
				System.out.println("Invalid Entry");
			}
			ps.close();
			co.close();
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		return rslt;
		
	}

	public int[] deleteMsgs(String[] selectedIds) {
		Connection con = ConnectionManager.getConnection();
		int[] del = null;
		try {
			PreparedStatement ps = con.prepareStatement("delete from mailbox where u_id= ?");
			for (String slctdids : selectedIds) {
				ps.setString(1, slctdids);
				ps.addBatch();
			}
			del = ps.executeBatch();
			if(del != null){
				System.out.println("deleted");
			}
			else{
				System.out.println("nt deleted");
			}
			ps.close();
			con.close();
		} 
		catch (SQLException e) {
			e.printStackTrace();
		}
		return del;
		
	}

	public int updateDetails(String addr, String fnm, String lnm, String phn, String uid) {
		Connection con = ConnectionManager.getConnection();
		int updtrslt=0;
		try {
			PreparedStatement ps = con.prepareStatement("update userinfo set "
					+ "first_name = ?, last_name = ?, phone = ?, address = ? where id = ? ");
			ps.setString(1, fnm);
			ps.setString(2, lnm);
			ps.setString(3, phn);
			ps.setString(4, addr);
			ps.setString(5, uid);
			System.out.println("inside db");
			updtrslt = ps.executeUpdate();
			if(updtrslt>0){
				System.out.println("Updatedted Sucessfully");
			}
			else{
				System.out.println("Invalid Entry");
			}
			ps.close();
			con.close();
		} 
		catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return updtrslt;
	}
	
	public static Map<String, String> usersMap(){
		Map<String, String> map = new HashMap<String, String>();
		Connection con = ConnectionManager.getConnection();
		try {
			PreparedStatement ps = con.prepareStatement("select id,first_name from userinfo");
			ResultSet rs =ps.executeQuery(); 
			while(rs.next()){
				 map.put(rs.getString("id"), rs.getString("first_name"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return map; 
	}

	public int expenseEntry(int uid, String date, String item, int price, int expense) {
		Connection con = ConnectionManager.getConnection();
		int rslt = 0;
		try {
			PreparedStatement ps = con.prepareStatement("insert into expensetable "
					+ "(uid, date, item, price, expense) values (?, ?, ?, ?, ?)");
			ps.setInt(1, uid);
			ps.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
			ps.setString(3, item);
			ps.setInt(4, price);
			ps.setInt(5, expense);
			rslt = ps.executeUpdate();
			ps.close();
			if(rslt>0){
				System.out.println("Inserted Sucessfully");
			}
			else{
				System.out.println("Invalid Entry");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return rslt;
	}
	
	public List<ExpenseInfo> displayExpnsRprt(int uid){
		Connection con = ConnectionManager.getConnection();
		List<ExpenseInfo> lstexpinfo = new ArrayList<ExpenseInfo>();
		try {
			PreparedStatement ps = con.prepareStatement("select * from expensetable where uid='"+uid+"'");
			ResultSet rs = ps.executeQuery();
			ExpenseInfo expinfo = null;
			while(rs.next()){
				expinfo = new ExpenseInfo();
				//int id = rs.getInt("id");
				expinfo.setDate(rs.getString("date"));
				expinfo.setItem(rs.getString("item"));
				expinfo.setPrice(rs.getInt("price"));
				expinfo.setTexpense(rs.getInt("expense"));
				lstexpinfo.add(expinfo);
			}
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return lstexpinfo;
	}
}
